﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;
using NHibernate.Context;
using Eg.Core;
using NHibernate.Criterion;
using NHibernate.Criterion.Lambda;
using NHibernate.SqlCommand;

namespace NhConfig
{
    public class Queries
    {
        private readonly ISession _session;

        public Queries(ISession session)
        {
            if (session == null)
                throw new ArgumentNullException("Session");
            _session = session;
        }

        public IEnumerable<Movie> GetMovieDirectorBy(string directName)
        {
            try
            {
                
                string hsql = @"from Movie m where m.Director = :director";
                //return _session.QueryOver<Movie>().Where(m => m.Director == directName).List();
                return _session.CreateQuery(hsql).SetString("director", directName).List<Movie>();

            }
            catch (Exception ex)
            {
                return _session.CreateCriteria<Movie>().Add(Restrictions.Eq("Director", directName)).List<Movie>();
            }
        }

        public IEnumerable<Movie> GetMovieWith(string actorName)
        {
            //return _session.CreateCriteria<Movie>().CreateCriteria("Actors", JoinType.InnerJoin).Add(Restrictions.Eq("Actor", actorName)).List<Movie>();
            //return _session.QueryOver<Movie>()
            //    .OrderBy(m => m.UnitPrice).Asc
            //    .Inner.JoinQueryOver<ActorRole>(m => m.Actors)
            //    .Where(a => a.Actor == actorName)
            //    .List();
            string hql = @"select m from Movie m inner join m.Actors as ar where ar.Actor = :actorName";
            return _session.CreateQuery(hql)
                .SetString("actorName", actorName)
                .List<Movie>();
        }

        public Book GetBookByISBN(string isbn)
        {
            //return _session.CreateCriteria<Book>().Add(Restrictions.Eq("ISBN", isbn)).UniqueResult<Book>();
            return _session.GetNamedQuery("GetBookByISBN")
                .SetString("isbn", isbn)
                .UniqueResult<Book>();
        }

        public IEnumerable<Product> GetProductByPrice(decimal minPrice,decimal maxPrice)
        {
            return _session.CreateCriteria<Product>().Add(Restrictions.And(Restrictions.Ge("UnitPrice", minPrice), Restrictions.Le("UnitPrice", maxPrice))).AddOrder(Order.Asc("UnitPrice")).List<Product>();
        }

        public IEnumerable<NameAndPrice> GetMoviePriceList()
        {
            return _session.QueryOver<Movie>()
                .Select(m => m.Name, m => m.UnitPrice)
                .List<object[]>()
                .Select(prop => new NameAndPrice()
                {
                    Name = (string)prop[0],
                    Price = (decimal)prop[1]
                });
        }

        public decimal GetAverageMovicePrice()
        {
            var result = _session.QueryOver<Movie>()
                .Select(Projections.Avg<Movie>(m => m.UnitPrice)).SingleOrDefault<double>();
            return Convert.ToDecimal(result);
        }

        public PageOf<Product> GetPageOfProducts(int pageNumber, int pageSize)
        {
            var skip = (pageNumber - 1) * pageSize;
            var countQuery = GetCountQuery();
            var resultQuery = GetPageQuery(skip, pageSize);
            var muliCrt = _session.CreateMultiCriteria().Add<int>("count", countQuery)
                .Add<Product>("page", resultQuery);
            var productCount = ((IList<int>)muliCrt.GetResult("count")).Single();
            var products = (IList<Product>)muliCrt.GetResult("page");
            var pageCount = (int)Math.Ceiling(productCount / (double)pageSize);
            return new PageOf<Product>() { 
             PageCount = pageCount, 
             PageOfResult = products, 
             PageNumber = pageNumber
            };
        }

        private ICriteria GetCountQuery()
        {
            return _session.QueryOver<Product>()
                .SelectList(list => list.SelectCount(m => m.Id)).UnderlyingCriteria;
        }

        public ICriteria GetPageQuery(int skip,int take)
        {
            return _session.QueryOver<Product>()
                .OrderBy(m => m.UnitPrice).Asc
                .Skip(skip)
                .Take(take)
                .UnderlyingCriteria;
        }
    }
}
